﻿using System;
using System.Data;
using System.Data.SqlClient;

namespace UniversityStudentManagemnetSystem.DAO.DAL.Gateway
{
    public class DepartmentGateway:DAO.Gateway.Gateway
    {
        public bool Save(Department department)
        {
            try
            {
                Connection.Open();
                string insertQuery = string.Format("insert into t_Department values(@code,@name)");
                Command.CommandText = insertQuery;
                Command.Parameters.Clear();
                Command.Parameters.Add("@name", SqlDbType.VarChar);
                Command.Parameters["@name"].Value = department.Name;
                Command.Parameters.Add("@code", SqlDbType.VarChar);
                Command.Parameters["@code"].Value = department.Code;
                Command.ExecuteNonQuery();
                
            }
            catch (Exception exception)
            {
                throw new Exception("Save couldn't performed due to system error!", exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }

            return true;
        }
        public DataTable GetDepartments()
        {
            string selectQuery = string.Format("select Code, Name from t_Department");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery,GetConnectionString());
            DataSet myDataset = new DataSet();
            sqlDataAdapter.Fill(myDataset, "DeparmentTable");
            DataTable myDataTable = myDataset.Tables["DeparmentTable"];
            return myDataTable;
        }
        public DataTable GetAllDepartments()
        {
            string selectQuery = string.Format("select * from t_Department");
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(selectQuery, GetConnectionString());
            DataSet myDataset = new DataSet();
            sqlDataAdapter.Fill(myDataset, "DeparmentTable");
            DataTable myDataTable = myDataset.Tables["DeparmentTable"];
            return myDataTable;
        }
        public bool HasThisDepartmentName(string name)
        {
            bool hasRows = false;
            try
            {
                Connection.Open();
                string query = String.Format("SELECT * FROM t_Department WHERE Name=@name");
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@name", SqlDbType.VarChar);
                Command.Parameters["@name"].Value = name;
                SqlDataReader reader = Command.ExecuteReader();
               

                if (reader != null)
                {
                    hasRows = reader.HasRows;
                }

                Connection.Close();
            }
            catch (FormatException exception)
            {
                throw new Exception(exception.Message);
            }
            catch (Exception exception)
            {

                throw new Exception("Sorry!System Error!Please Try Again!",exception);
            }
            finally
            {
                if (Connection != null && Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
           
            return hasRows;
        }

        public bool HasThisDepartmentCode(string code)
        {
            bool hasRows = false;
            try
            {

                Connection.Open();
                string query = String.Format("SELECT * FROM t_Department WHERE Code=@code");
                Command.CommandText = query;
                Command.Parameters.Clear();
                Command.Parameters.Add("@code", SqlDbType.VarChar);
                Command.Parameters["@code"].Value = code;
                SqlDataReader reader = Command.ExecuteReader();
             

                if (reader != null)
                {
                    hasRows = reader.HasRows;
                }
                
            }
                catch(FormatException exception)
                {
                    throw new Exception(exception.Message);
                }

            catch (Exception exception)
            {
                
                throw new Exception("Sorry!System Error!Please Try Again!",exception);
            }
            
            finally
            {
                if (Connection!=null && Connection.State==ConnectionState.Open)
                {
                    Connection.Close();
                }
            }
            return hasRows;
        }
    }
}